{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   rank vol_party_name     vol  vol_chg long_party_name long_open_interest  \\\n",
      "0     1           海通期货  77,518  -14,514            鲁证期货              8,021   \n",
      "1     2           光大期货  22,475   -3,594            中信期货              7,515   \n",
      "2     3           中信期货  20,672   -5,395            华泰期货              6,375   \n",
      "3     4           徽商期货  19,456   -4,185            国泰君安              6,326   \n",
      "4     5           华闻期货  17,171   -3,880            光大期货              6,060   \n",
      "5     6           华泰期货  17,114   -3,606            东证期货              5,499   \n",
      "6     7           东吴期货  16,141   -7,227            徽商期货              4,583   \n",
      "7     8           东证期货  14,896   -6,157            方正中期              3,971   \n",
      "8     9           华安期货  14,397   -1,340            宏源期货              3,824   \n",
      "9    10           新湖期货  11,895    2,127            海通期货              3,565   \n",
      "10   11           国泰君安  11,140     -607            长安期货              3,500   \n",
      "11   12           中辉期货  10,869      457            银河期货              3,434   \n",
      "12   13           方正中期   9,417   -1,887          国投安信期货              3,400   \n",
      "13   14           宏源期货   8,879   -2,445            永安期货              2,899   \n",
      "14   15           银河期货   7,739   -3,829            中辉期货              2,523   \n",
      "15   16           鲁证期货   7,169   -2,878            东吴期货              2,402   \n",
      "16   17           东方财富   7,141   -5,623            安粮期货              2,372   \n",
      "17   18           永安期货   7,098   -1,046            国信期货              2,333   \n",
      "18   19           国信期货   6,887   -1,818            兴证期货              2,324   \n",
      "19   20           招金期货   6,486   -1,693            华安期货              2,313   \n",
      "\n",
      "   long_open_interest_chg short_party_name short_open_interest  \\\n",
      "0                      60             永安期货               9,572   \n",
      "1                  -1,161             中信期货               6,691   \n",
      "2                    -332             东证期货               6,119   \n",
      "3                   1,427             华泰期货               6,067   \n",
      "4                    -894             光大期货               6,028   \n",
      "5                     531             国泰君安               4,238   \n",
      "6                    -137             浙商期货               3,620   \n",
      "7                     794             徽商期货               3,470   \n",
      "8                      53           国投安信期货               3,202   \n",
      "9                     110             海通期货               3,064   \n",
      "10                    524             华安期货               3,036   \n",
      "11                   -154             银河期货               3,027   \n",
      "12                   -272             宏源期货               3,022   \n",
      "13                    214             安粮期货               2,980   \n",
      "14                   -269             南华期货               2,753   \n",
      "15                    312             招金期货               2,735   \n",
      "16                    -51             东吴期货               2,666   \n",
      "17                   -372             申银万国               2,633   \n",
      "18                    -13             华闻期货               2,610   \n",
      "19                     76             方正中期               2,377   \n",
      "\n",
      "   short_open_interest_chg symbol variety      date  \n",
      "0                     -100     AP      AP  20201019  \n",
      "1                   -1,395     AP      AP  20201019  \n",
      "2                     -899     AP      AP  20201019  \n",
      "3                       42     AP      AP  20201019  \n",
      "4                     -639     AP      AP  20201019  \n",
      "5                    1,203     AP      AP  20201019  \n",
      "6                      213     AP      AP  20201019  \n",
      "7                       37     AP      AP  20201019  \n",
      "8                       64     AP      AP  20201019  \n",
      "9                     -302     AP      AP  20201019  \n",
      "10                    -347     AP      AP  20201019  \n",
      "11                    -735     AP      AP  20201019  \n",
      "12                     412     AP      AP  20201019  \n",
      "13                     143     AP      AP  20201019  \n",
      "14                     -82     AP      AP  20201019  \n",
      "15                    -990     AP      AP  20201019  \n",
      "16                     463     AP      AP  20201019  \n",
      "17                    -200     AP      AP  20201019  \n",
      "18                     678     AP      AP  20201019  \n",
      "19                    -149     AP      AP  20201019  \n",
      "20201019 CZC 数据异常\n",
      "20201020 CZC 数据异常\n",
      "    symbol  rank vol_party_name     vol  vol_chg long_party_name  \\\n",
      "0   cu2011     1           华泰期货   49275    -6798            金瑞期货   \n",
      "1   cu2011     2           东证期货   18171    -1504            中信期货   \n",
      "2   cu2011     3           国泰君安   12592     4608            广发期货   \n",
      "3   cu2011     4           中信期货   10819     -315            五矿经易   \n",
      "4   cu2011     5           五矿经易   10680      304            国投安信   \n",
      "5   cu2011     6           海通期货    9368    -5895            国贸期货   \n",
      "6   cu2011     7           国富期货    9283      615            海通期货   \n",
      "7   cu2011     8           金瑞期货    6413      -82            兴业期货   \n",
      "8   cu2011     9           国信期货    5563    -1316            永安期货   \n",
      "9   cu2011    10           国投安信    4392      467            铜冠金源   \n",
      "10  cu2011    11           弘业期货    4273     -549            中航期货   \n",
      "11  cu2011    12           银河期货    4024      171            华安期货   \n",
      "12  cu2011    13           中辉期货    3229    -2647            格林大华   \n",
      "13  cu2011    14           华安期货    3136      325            银河期货   \n",
      "14  cu2011    15           广州期货    2754     1694            建信期货   \n",
      "15  cu2011    16           兴证期货    2602    -1135            中粮期货   \n",
      "16  cu2011    17           混沌天成    2505     1556            弘业期货   \n",
      "17  cu2011    18           国贸期货    2423      359            华泰期货   \n",
      "18  cu2011    19           华闻期货    2384      669            新湖期货   \n",
      "19  cu2011    20           一德期货    2342      438            国泰君安   \n",
      "20  cu2011   999           None  166228    -9035            None   \n",
      "\n",
      "    long_open_interest  long_open_interest_chg short_party_name  \\\n",
      "0                17933                    -477             金瑞期货   \n",
      "1                11596                    -752             国泰君安   \n",
      "2                 9631                      45             兴业期货   \n",
      "3                 7795                      30             中信期货   \n",
      "4                 4545                    -476             云晨期货   \n",
      "5                 3421                     415             一德期货   \n",
      "6                 2944                    -563             铜冠金源   \n",
      "7                 2422                     -17             五矿经易   \n",
      "8                 2195                     337             浙商期货   \n",
      "9                 2151                    -251             国贸期货   \n",
      "10                2149                      -6             银河期货   \n",
      "11                1985                     483             中信建投   \n",
      "12                1976                     306             永安期货   \n",
      "13                1970                    -393             建信期货   \n",
      "14                1843                      88             海通期货   \n",
      "15                1619                      84             东证期货   \n",
      "16                1499                     -10             混沌天成   \n",
      "17                1475                      95             华泰期货   \n",
      "18                1438                      31             上海大陆   \n",
      "19                1349                    -110             迈科期货   \n",
      "20               81936                   -1141             None   \n",
      "\n",
      "    short_open_interest  short_open_interest_chg variety      date  \n",
      "0               10223.0                   -538.0      CU  20201019  \n",
      "1                4606.0                    -86.0      CU  20201019  \n",
      "2                4605.0                    199.0      CU  20201019  \n",
      "3                4499.0                   -121.0      CU  20201019  \n",
      "4                3993.0                   -292.0      CU  20201019  \n",
      "5                3392.0                   -712.0      CU  20201019  \n",
      "6                3041.0                   -178.0      CU  20201019  \n",
      "7                2975.0                   -148.0      CU  20201019  \n",
      "8                2943.0                   -231.0      CU  20201019  \n",
      "9                2823.0                     34.0      CU  20201019  \n",
      "10               2761.0                    -47.0      CU  20201019  \n",
      "11               2675.0                   -271.0      CU  20201019  \n",
      "12               2540.0                   -102.0      CU  20201019  \n",
      "13               2525.0                   -348.0      CU  20201019  \n",
      "14               2518.0                    555.0      CU  20201019  \n",
      "15               2328.0                    606.0      CU  20201019  \n",
      "16               2130.0                     -2.0      CU  20201019  \n",
      "17               1852.0                   -210.0      CU  20201019  \n",
      "18               1821.0                   -196.0      CU  20201019  \n",
      "19               1764.0                    -16.0      CU  20201019  \n",
      "20              66014.0                  -2104.0      CU  20201019  \n",
      "20201019 SHF 数据异常\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    long_open_interest  long_open_interest_chg long_party_name  rank  \\\n",
      "0                 8043                    -291            中信期货     1   \n",
      "1                 7586                     -68            国泰君安     2   \n",
      "2                 5434                    -218            瑞银期货     3   \n",
      "3                 5150                     -59            海通期货     4   \n",
      "4                 2976                      37            一德期货     5   \n",
      "5                 2777                     -52            申银万国     6   \n",
      "6                 2455                      65            华泰期货     7   \n",
      "7                 2264                    -155            广发期货     8   \n",
      "8                 2158                      49            永安期货     9   \n",
      "9                 2058                     155            上海东证    10   \n",
      "10                1815                       1            中金期货    11   \n",
      "11                1656                     120            银河期货    12   \n",
      "12                1541                      55            浙商期货    13   \n",
      "13                1501                      33            国投安信    14   \n",
      "14                1285                    -121            兴证期货    15   \n",
      "15                1222                     -58            中粮期货    16   \n",
      "16                1191                      -8            大越期货    17   \n",
      "17                1175                      95            大地期货    18   \n",
      "18                1096                     -13            中银期货    19   \n",
      "19                1029                     108            中信建投    20   \n",
      "20               54412                    -325            None   999   \n",
      "\n",
      "    short_open_interest  short_open_interest_chg short_party_name  symbol  \\\n",
      "0                 12522                      155             上海东证  IF2012   \n",
      "1                 12189                     -302             中信期货  IF2012   \n",
      "2                  6099                     -119             华泰期货  IF2012   \n",
      "3                  5755                     -746             国泰君安  IF2012   \n",
      "4                  3778                       84             中信建投  IF2012   \n",
      "5                  3164                     -154             海通期货  IF2012   \n",
      "6                  3018                       12             平安期货  IF2012   \n",
      "7                  2253                      -23             招商期货  IF2012   \n",
      "8                  2241                      -33             永安期货  IF2012   \n",
      "9                  2227                     -117             国投安信  IF2012   \n",
      "10                 1981                       49             银河期货  IF2012   \n",
      "11                 1677                        8             光大期货  IF2012   \n",
      "12                 1652                      -59             建信期货  IF2012   \n",
      "13                 1515                       71             国信期货  IF2012   \n",
      "14                 1430                      117             申银万国  IF2012   \n",
      "15                 1427                      -22             国富期货  IF2012   \n",
      "16                 1316                     -118             兴业期货  IF2012   \n",
      "17                 1103                       92             华闻期货  IF2012   \n",
      "18                 1081                     -171             兴证期货  IF2012   \n",
      "19                 1025                     -117             南华期货  IF2012   \n",
      "20                67453                    -1393             None  IF2012   \n",
      "\n",
      "      vol  vol_chg vol_party_name variety      date  \n",
      "0    5191     1093           中信期货      IF  20201019  \n",
      "1    5098     1441           国泰君安      IF  20201019  \n",
      "2    3015      614           海通期货      IF  20201019  \n",
      "3    2830      670           华闻期货      IF  20201019  \n",
      "4    2198      379           五矿经易      IF  20201019  \n",
      "5    2184      352           兴证期货      IF  20201019  \n",
      "6    1924       93           华泰期货      IF  20201019  \n",
      "7    1821      519           银河期货      IF  20201019  \n",
      "8    1802      -75           上海东证      IF  20201019  \n",
      "9    1596      117           东兴期货      IF  20201019  \n",
      "10   1595      938           一德期货      IF  20201019  \n",
      "11   1415      274           华西期货      IF  20201019  \n",
      "12   1394      314           国信期货      IF  20201019  \n",
      "13   1332      128           国投安信      IF  20201019  \n",
      "14   1207       92           西部期货      IF  20201019  \n",
      "15   1158      192           南华期货      IF  20201019  \n",
      "16   1124      148           中信建投      IF  20201019  \n",
      "17   1020      334           上海中期      IF  20201019  \n",
      "18   1012      173           永安期货      IF  20201019  \n",
      "19   1009      203           方正中期      IF  20201019  \n",
      "20  39925     7999           None      IF  20201019  \n",
      "20201019 CFE 数据异常\n",
      "    long_open_interest  long_open_interest_chg long_party_name  rank  \\\n",
      "0                11045                    4037            华安期货     1   \n",
      "1                 6019                  -12591            国投安信     2   \n",
      "2                 5097                     766            国泰君安     3   \n",
      "3                 4905                    4215            广发期货     4   \n",
      "4                 2496                    2167            兴证期货     5   \n",
      "5                 1558                    1515            中信期货     6   \n",
      "6                 1085                     715            北京首创     7   \n",
      "7                 1041                      -1            东证期货     8   \n",
      "8                  802                    -146            冠通期货     9   \n",
      "9                  669                     318            中辉期货    10   \n",
      "10                 600                     300            国联期货    11   \n",
      "11                 520                     256            中粮期货    12   \n",
      "12                 265                      12            新湖期货    13   \n",
      "13                 240                       0            南华期货    14   \n",
      "14                 234                   -1352            永安期货    15   \n",
      "15                 202                       0            迈科期货    16   \n",
      "16                 180                       0            山金期货    17   \n",
      "17                 172                     149            华泰期货    18   \n",
      "18                 162                      65            中信建投    19   \n",
      "19                 130                     -30            兴业期货    20   \n",
      "20               37422                     395            None   999   \n",
      "\n",
      "    short_open_interest  short_open_interest_chg short_party_name    vol  \\\n",
      "0                  5277                      402             中信期货  16783   \n",
      "1                  4873                      -49             银河期货  12402   \n",
      "2                  4389                     -500             民生期货   5419   \n",
      "3                  3857                     1932             国投安信   4260   \n",
      "4                  3755                      199             一德期货   3404   \n",
      "5                  3073                      -11             中粮期货   3111   \n",
      "6                  1608                     -280             永安期货   2077   \n",
      "7                  1604                        0             兴业期货   2024   \n",
      "8                   896                       -1             长安期货   1171   \n",
      "9                   843                      -20             光大期货   1127   \n",
      "10                  750                      -12             广州金控    970   \n",
      "11                  730                    -1109             金瑞期货    881   \n",
      "12                  720                        0             九州期货    672   \n",
      "13                  704                       -1             鲁证期货    579   \n",
      "14                  700                        0             中融汇信    505   \n",
      "15                  615                       -7             国泰君安    439   \n",
      "16                  487                      -45             新湖期货    424   \n",
      "17                  465                       -8             徽商期货    392   \n",
      "18                  382                      122             华泰期货    262   \n",
      "19                  234                        0             浙商期货    231   \n",
      "20                35962                      612             None  57133   \n",
      "\n",
      "    vol_chg vol_party_name symbol variety      date  \n",
      "0     15731           国投安信  c2011       C  20201019  \n",
      "1      6309           冠通期货  c2011       C  20201019  \n",
      "2      3589           华安期货  c2011       C  20201019  \n",
      "3      4063           广发期货  c2011       C  20201019  \n",
      "4      3085           兴证期货  c2011       C  20201019  \n",
      "5      2515           金瑞期货  c2011       C  20201019  \n",
      "6      -571           中信期货  c2011       C  20201019  \n",
      "7      1524           永安期货  c2011       C  20201019  \n",
      "8       788           北京首创  c2011       C  20201019  \n",
      "9       753           中辉期货  c2011       C  20201019  \n",
      "10      834           中信建投  c2011       C  20201019  \n",
      "11     -963           国泰君安  c2011       C  20201019  \n",
      "12      334           光大期货  c2011       C  20201019  \n",
      "13      530           华泰期货  c2011       C  20201019  \n",
      "14      104           民生期货  c2011       C  20201019  \n",
      "15      -29           中粮期货  c2011       C  20201019  \n",
      "16      357           创元期货  c2011       C  20201019  \n",
      "17     -188           国联期货  c2011       C  20201019  \n",
      "18      249           一德期货  c2011       C  20201019  \n",
      "19       22           宏源期货  c2011       C  20201019  \n",
      "20    39036           None  c2011       C  20201019  \n",
      "20201019 DCE 数据异常\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# encoding: utf-8\n",
    "# import tensorflow as tf\n",
    "import datetime,time\n",
    "import pandas as pd\n",
    "import json\n",
    "from pymongo import MongoClient\n",
    "import akshare as ak\n",
    "from pandas import DataFrame\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "def get_trade_rank(market = 'SHF', date = None):\n",
    "    if date is None:\n",
    "        date = get_target_date(-1, \"%Y-%m-%d\")\n",
    "    if market == 'SHF':\n",
    "        return ak.get_shfe_rank_table(date)\n",
    "    if market == 'DCE':\n",
    "        return ak.get_dce_rank_table(date)\n",
    "    if market == 'CZC':\n",
    "        return ak.get_czce_rank_table(date)\n",
    "    if market == \"CFE\":\n",
    "        return ak.get_cffex_rank_table(date)\n",
    "    return None, '不支持的市场类型'\n",
    "\n",
    "if __name__ == '__main__':\n",
    "\n",
    "    markets = ['CZC', 'SHF','CFE','DCE']#, 'CZC', 'SHF','CFE','DCE'\n",
    "    # 连接数据库\n",
    "    client = MongoClient('localhost', 27017)\n",
    "    db = client.futures3\n",
    "    position = db.position\n",
    "    \n",
    "    df=pd.DataFrame()\n",
    "    for market in markets:\n",
    "        begin = datetime.date(2020,10,19)\n",
    "        # end = datetime.date(2020,2,25)\n",
    "        end = datetime.date.today()\n",
    "\n",
    "#         begin = DataFrame(list(position.find({}).sort([('_id', -1)]).limit(1)))\n",
    "        # begin = begin['date'][0]\n",
    "        # begin = time.strptime(begin, \"%Y%m%d\")\n",
    "        # year, month, day = begin[:3]\n",
    "        # begin = datetime.date(year, month, day)\n",
    "        # begin = begin + datetime.timedelta(days=1)\n",
    "\n",
    "        for i in range((end - begin).days + 1):\n",
    "            day = begin + datetime.timedelta(days=i)\n",
    "            days=day.strftime('%Y%m%d')\n",
    "            try:\n",
    "                df = get_trade_rank(market, date=days)\n",
    "                # print(days, market)\n",
    "                for key, value in df.items():\n",
    "                    value['date'] = days\n",
    "                    print(value)\n",
    "                    value['symbol']=value['symbol'].str.upper()\n",
    "                    value= value[value['long_party_name'].notna()]\n",
    "                    # print(value)\n",
    "                    # position.insert_many(json.loads(value.T.to_json()).values())\n",
    "                    # print(json.loads(value.T.to_json()).values())\n",
    "                    #去除具体合约。因汇总持仓有问题\n",
    "#                     if market != 'CZC':\n",
    "#                         print('insert into',key)\n",
    "#                         # position.update_one(value, {'$set': value}, True)\n",
    "#                         position.insert_many(json.loads(value.T.to_json()).values())\n",
    "\n",
    "#                     else:\n",
    "#                         value=value[value['symbol']==value['variety']]\n",
    "# #                         value= value[value['long_party_name'].notna()]\n",
    "#                         print('insert into',key)\n",
    "#                         df1 = pd.DataFrame(value)\n",
    "#         \n",
    "                    df = df.append(df1)\n",
    "#                         df=df.append(value)\n",
    "                        # position.update_one(value, {'$set': value}, True)\n",
    "                        # position.insert_many(json.loads(value.T.to_json()).values())\n",
    "#                         print(json.loads(value.T.to_json()).values())\n",
    "\n",
    "            except:\n",
    "                print(days,market,'数据异常')\n",
    "                continue\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>level_0</th>\n",
       "      <th>level_1</th>\n",
       "      <th>level_2</th>\n",
       "      <th>long_openIntr</th>\n",
       "      <th>short_openIntr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>一德期货</td>\n",
       "      <td>2457.0</td>\n",
       "      <td>2542.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>东吴期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>东海期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6118.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>东航期货</td>\n",
       "      <td>2976.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>东证期货</td>\n",
       "      <td>4193.0</td>\n",
       "      <td>7221.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>中信建投</td>\n",
       "      <td>4544.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>中信期货</td>\n",
       "      <td>4675.0</td>\n",
       "      <td>2067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>中财期货</td>\n",
       "      <td>3001.0</td>\n",
       "      <td>3495.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>中辉期货</td>\n",
       "      <td>2770.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>五矿经易</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2470.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>信达期货</td>\n",
       "      <td>8095.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>兴证期货</td>\n",
       "      <td>2941.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>华安期货</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>华泰期货</td>\n",
       "      <td>2899.0</td>\n",
       "      <td>7707.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>华融融达</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9045.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>南华期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2534.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>国投安信</td>\n",
       "      <td>2843.0</td>\n",
       "      <td>7527.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>国泰君安</td>\n",
       "      <td>4339.0</td>\n",
       "      <td>4707.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>国海良时</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>国贸期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>天风期货</td>\n",
       "      <td>4298.0</td>\n",
       "      <td>9418.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>宏源期货</td>\n",
       "      <td>2851.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>建信期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5046.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>徽商期货</td>\n",
       "      <td>4852.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>新湖期货</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2412.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>方正中期</td>\n",
       "      <td>3700.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>永安期货</td>\n",
       "      <td>8999.0</td>\n",
       "      <td>9692.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>浙商期货</td>\n",
       "      <td>5280.0</td>\n",
       "      <td>5527.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20201019</td>\n",
       "      <td>EB</td>\n",
       "      <td>银河期货</td>\n",
       "      <td>3697.0</td>\n",
       "      <td>7395.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     level_0 level_1 level_2  long_openIntr  short_openIntr\n",
       "0   20201019      EB    一德期货         2457.0          2542.0\n",
       "1   20201019      EB    东吴期货            0.0          3850.0\n",
       "2   20201019      EB    东海期货            0.0          6118.0\n",
       "3   20201019      EB    东航期货         2976.0             0.0\n",
       "4   20201019      EB    东证期货         4193.0          7221.0\n",
       "5   20201019      EB    中信建投         4544.0             0.0\n",
       "6   20201019      EB    中信期货         4675.0          2067.0\n",
       "7   20201019      EB    中财期货         3001.0          3495.0\n",
       "8   20201019      EB    中辉期货         2770.0             0.0\n",
       "9   20201019      EB    五矿经易            0.0          2470.0\n",
       "10  20201019      EB    信达期货         8095.0             0.0\n",
       "11  20201019      EB    兴证期货         2941.0             0.0\n",
       "12  20201019      EB    华安期货         3420.0             0.0\n",
       "13  20201019      EB    华泰期货         2899.0          7707.0\n",
       "14  20201019      EB    华融融达            0.0          9045.0\n",
       "15  20201019      EB    南华期货            0.0          2534.0\n",
       "16  20201019      EB    国投安信         2843.0          7527.0\n",
       "17  20201019      EB    国泰君安         4339.0          4707.0\n",
       "18  20201019      EB    国海良时            0.0          5255.0\n",
       "19  20201019      EB    国贸期货            0.0          2000.0\n",
       "20  20201019      EB    天风期货         4298.0          9418.0\n",
       "21  20201019      EB    宏源期货         2851.0             0.0\n",
       "22  20201019      EB    建信期货            0.0          5046.0\n",
       "23  20201019      EB    徽商期货         4852.0             0.0\n",
       "24  20201019      EB    新湖期货            0.0          2412.0\n",
       "25  20201019      EB    方正中期         3700.0             0.0\n",
       "26  20201019      EB    永安期货         8999.0          9692.0\n",
       "27  20201019      EB    浙商期货         5280.0          5527.0\n",
       "28  20201019      EB    银河期货         3697.0          7395.0"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " #所有会员\n",
    "# party_name = value[value['date'] == end]\n",
    "long_party_name = df['long_party_name']\n",
    "short_party_name = df['short_party_name']\n",
    "party_name = long_party_name.append(short_party_name).drop_duplicates()\n",
    "#多空变化量求和\n",
    "long = value.groupby(['date', 'variety', 'long_party_name'])['long_openIntr'].sum()\n",
    "# print(long)\n",
    "short = value.groupby(['date', 'variety', 'short_party_name'])['short_openIntr'].sum()\n",
    "# # 合并\n",
    "frames = [long, short]\n",
    "position = pd.concat(frames, axis=1, sort=True).fillna(0).reset_index()\n",
    "position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variety</th>\n",
       "      <th>中信期货</th>\n",
       "      <th>永安期货</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EB</td>\n",
       "      <td>-2608.0</td>\n",
       "      <td>693.0</td>\n",
       "      <td>-1915.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  variety    中信期货   永安期货   total\n",
       "0      EB -2608.0  693.0 -1915.0"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " # 字段更名\n",
    "position = position.rename(columns={'level_0': 'date', 'level_1': 'variety', 'level_2': 'BrokerID'})\n",
    "\n",
    "position['net_chg'] = position.apply(lambda x: x['short_openIntr'] - x['long_openIntr'], axis=1)\n",
    "# position\n",
    "party_names=['永安期货','海通期货','中信期货']\n",
    "\n",
    "df = pd.DataFrame()\n",
    "for i in party_names:\n",
    "    try:\n",
    "#     print(i)\n",
    "        mem = position[position['BrokerID'] == i]\n",
    "    \n",
    "        \n",
    "#         new_df = new_df.rename_axis(columns=None)\n",
    "#         new_df = new_df\n",
    "        df1 = pd.DataFrame(mem)\n",
    "#         \n",
    "        df = df.append(df1)\n",
    "        \n",
    "        \n",
    "    except:\n",
    "        print('?')\n",
    "        continue\n",
    "\n",
    "two_level_index_series = df.set_index(['variety','BrokerID'])['net_chg']\n",
    "new_df = two_level_index_series.unstack()\n",
    "new_df['total'] = new_df.apply(lambda x: x.sum(), axis=1)\n",
    "new_df = new_df.rename_axis(columns=None).reset_index()\n",
    "# \n",
    "new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.to_excel('E:\\pp.xlsx',index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
